﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;

namespace Chenxin.Lib.Business.DAL
{
    public class ContractFinanceBillInfo
    {
        public ContractFinanceBillInfo()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public static bool Exists(Guid BillId)
        {
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business_Search");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_Contract_Finance_BillInfo where BillId=@BillId ");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "BillId", DbType.Guid, BillId);
            int cmdresult;
            object obj = db.ExecuteScalar(dbCommand);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 生成开单编号
        /// </summary>
        public static string GenerateCode(Guid CompanyId)
        {
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business_Search");
            DbCommand dbCommand = db.GetStoredProcCommand("usp_AutoGenerateBillCode");
            db.AddInParameter(dbCommand, "CompanyId", DbType.Guid, CompanyId);
            db.AddOutParameter(dbCommand, "BillCode", DbType.AnsiString, 150);
            db.ExecuteNonQuery(dbCommand);
            return db.GetParameterValue(dbCommand, "BillCode").ToString();
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static void Add(Chenxin.Lib.Business.Entity.ContractFinanceBillInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into t_Contract_Finance_BillInfo(");
            strSql.Append("BillId,ContractId,ContractCityId,ContractCity,ContractCompanyId,ContractCompany,ContractCode,BillCode,InOrOut,FundName,CustomerName,BillUnit,BillMoney,BillDate,Status,BillMemo,UnusedReason,UnusedDate,UnusedPersonId,UnusedPerson,BillDepartId,BillDepartName,BillCompanyId,BillCompany,BillCityId,BillCity,InputTime,BillOprEmpId,BillOprEmpName,BillOprDepartId,BillOprDepartName,PayDate,PayMoney,RemainMoney,PayOprEmpId,PayOprEmpName,PayOprEmpDepartId,PayOprEmpDepart,InvoiceCode,InvoiceDate,InvoiceMemo,InvoiceOprEmpId,InvoiceOprEmp,InvoiceOprDate,IsFinish,AchievementDate,LastCreateArchDate,LastCreateArchEmp,AchievementYear,AchievementMonth,FundManageType,OrderPlaceId,OrderPlace,BillPlaceId,BillPlace,PayPlaceId,PayPlace,IsRecheck,PayBankType,PayBankName,PayBankNo)");

            strSql.Append(" values (");
            strSql.Append("@BillId,@ContractId,@ContractCityId,@ContractCity,@ContractCompanyId,@ContractCompany,@ContractCode,@BillCode,@InOrOut,@FundName,@CustomerName,@BillUnit,@BillMoney,@BillDate,@Status,@BillMemo,@UnusedReason,@UnusedDate,@UnusedPersonId,@UnusedPerson,@BillDepartId,@BillDepartName,@BillCompanyId,@BillCompany,@BillCityId,@BillCity,@InputTime,@BillOprEmpId,@BillOprEmpName,@BillOprDepartId,@BillOprDepartName,@PayDate,@PayMoney,@RemainMoney,@PayOprEmpId,@PayOprEmpName,@PayOprEmpDepartId,@PayOprEmpDepart,@InvoiceCode,@InvoiceDate,@InvoiceMemo,@InvoiceOprEmpId,@InvoiceOprEmp,@InvoiceOprDate,@IsFinish,@AchievementDate,@LastCreateArchDate,@LastCreateArchEmp,@AchievementYear,@AchievementMonth,@FundManageType,@OrderPlaceId,@OrderPlace,@BillPlaceId,@BillPlace,@PayPlaceId,@PayPlace,@IsRecheck,@PayBankType,@PayBankName,@PayBankNo)");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "BillId", DbType.Guid, model.BillId);
            db.AddInParameter(dbCommand, "ContractId", DbType.Guid, model.ContractId);
            db.AddInParameter(dbCommand, "ContractCityId", DbType.Guid, model.ContractCityId);
            db.AddInParameter(dbCommand, "ContractCity", DbType.AnsiString, model.ContractCity);
            db.AddInParameter(dbCommand, "ContractCompanyId", DbType.Guid, model.ContractCompanyId);
            db.AddInParameter(dbCommand, "ContractCompany", DbType.AnsiString, model.ContractCompany);
            db.AddInParameter(dbCommand, "ContractCode", DbType.AnsiString, model.ContractCode);
            db.AddInParameter(dbCommand, "BillCode", DbType.AnsiString, model.BillCode);
            db.AddInParameter(dbCommand, "InOrOut", DbType.Int32, model.InOrOut);
            db.AddInParameter(dbCommand, "FundName", DbType.AnsiString, model.FundName);
            db.AddInParameter(dbCommand, "CustomerName", DbType.AnsiString, model.CustomerName);
            db.AddInParameter(dbCommand, "BillUnit", DbType.AnsiString, model.BillUnit);
            db.AddInParameter(dbCommand, "BillMoney", DbType.Currency, model.BillMoney);
            db.AddInParameter(dbCommand, "BillDate", DbType.DateTime, model.BillDate);
            db.AddInParameter(dbCommand, "Status", DbType.Int32, model.Status);
            db.AddInParameter(dbCommand, "BillMemo", DbType.AnsiString, model.BillMemo);
            db.AddInParameter(dbCommand, "UnusedReason", DbType.AnsiString, model.UnusedReason);
            db.AddInParameter(dbCommand, "UnusedDate", DbType.DateTime, model.UnusedDate);
            db.AddInParameter(dbCommand, "UnusedPersonId", DbType.Guid, model.UnusedPersonId);
            db.AddInParameter(dbCommand, "UnusedPerson", DbType.AnsiString, model.UnusedPerson);
            db.AddInParameter(dbCommand, "BillDepartId", DbType.Guid, model.BillDepartId);
            db.AddInParameter(dbCommand, "BillDepartName", DbType.AnsiString, model.BillDepartName);
            db.AddInParameter(dbCommand, "BillCompanyId", DbType.Guid, model.BillCompanyId);
            db.AddInParameter(dbCommand, "BillCompany", DbType.AnsiString, model.BillCompany);
            db.AddInParameter(dbCommand, "BillCityId", DbType.Guid, model.BillCityId);
            db.AddInParameter(dbCommand, "BillCity", DbType.AnsiString, model.BillCity);
            db.AddInParameter(dbCommand, "InputTime", DbType.DateTime, model.InputTime);
            db.AddInParameter(dbCommand, "BillOprEmpId", DbType.Guid, model.BillOprEmpId);
            db.AddInParameter(dbCommand, "BillOprEmpName", DbType.AnsiString, model.BillOprEmpName);
            db.AddInParameter(dbCommand, "BillOprDepartId", DbType.Guid, model.BillOprDepartId);
            db.AddInParameter(dbCommand, "BillOprDepartName", DbType.AnsiString, model.BillOprDepartName);
            db.AddInParameter(dbCommand, "PayDate", DbType.DateTime, model.PayDate);
            db.AddInParameter(dbCommand, "PayMoney", DbType.Decimal, model.PayMoney);
            db.AddInParameter(dbCommand, "RemainMoney", DbType.Decimal, model.RemainMoney);
            db.AddInParameter(dbCommand, "PayOprEmpId", DbType.Guid, model.PayOprEmpId);
            db.AddInParameter(dbCommand, "PayOprEmpName", DbType.AnsiString, model.PayOprEmpName);
            db.AddInParameter(dbCommand, "PayOprEmpDepartId", DbType.Guid, model.PayOprEmpDepartId);
            db.AddInParameter(dbCommand, "PayOprEmpDepart", DbType.AnsiString, model.PayOprEmpDepart);
            db.AddInParameter(dbCommand, "InvoiceCode", DbType.AnsiString, model.InvoiceCode);
            db.AddInParameter(dbCommand, "InvoiceDate", DbType.DateTime, model.InvoiceDate);
            db.AddInParameter(dbCommand, "InvoiceMemo", DbType.AnsiString, model.InvoiceMemo);
            db.AddInParameter(dbCommand, "InvoiceOprEmpId", DbType.Guid, model.InvoiceOprEmpId);
            db.AddInParameter(dbCommand, "InvoiceOprEmp", DbType.AnsiString, model.InvoiceOprEmp);
            db.AddInParameter(dbCommand, "InvoiceOprDate", DbType.DateTime, model.InvoiceOprDate);
            db.AddInParameter(dbCommand, "IsFinish", DbType.Boolean, model.IsFinish);
            db.AddInParameter(dbCommand, "AchievementDate", DbType.AnsiString, model.AchievementDate);
            db.AddInParameter(dbCommand, "LastCreateArchDate", DbType.DateTime, model.LastCreateArchDate);
            db.AddInParameter(dbCommand, "LastCreateArchEmp", DbType.AnsiString, model.LastCreateArchEmp);
            db.AddInParameter(dbCommand, "AchievementYear", DbType.Int32, model.AchievementYear);
            db.AddInParameter(dbCommand, "AchievementMonth", DbType.Int32, model.AchievementMonth);
            db.AddInParameter(dbCommand, "FundManageType", DbType.AnsiString, model.FundManageType);
            db.AddInParameter(dbCommand, "OrderPlaceId", DbType.Guid, model.OrderPlaceId);
            db.AddInParameter(dbCommand, "OrderPlace", DbType.AnsiString, model.OrderPlace);
            db.AddInParameter(dbCommand, "BillPlaceId", DbType.Guid, model.BillPlaceId);
            db.AddInParameter(dbCommand, "BillPlace", DbType.AnsiString, model.BillPlace);
            db.AddInParameter(dbCommand, "PayPlaceId", DbType.Guid, model.PayPlaceId);
            db.AddInParameter(dbCommand, "PayPlace", DbType.AnsiString, model.PayPlace);
            db.AddInParameter(dbCommand, "IsRecheck", DbType.Boolean, model.IsRecheck);
            db.AddInParameter(dbCommand, "PayBankType", DbType.AnsiString, model.PayBankType);
            db.AddInParameter(dbCommand, "PayBankName", DbType.AnsiString, model.PayBankName);
            db.AddInParameter(dbCommand, "PayBankNo", DbType.AnsiString, model.PayBankNo);
            db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static void Update(Chenxin.Lib.Business.Entity.ContractFinanceBillInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_Contract_Finance_BillInfo set ");
            strSql.Append("ContractId=@ContractId,");
            strSql.Append("ContractCityId=@ContractCityId,");
            strSql.Append("ContractCity=@ContractCity,");
            strSql.Append("ContractCompanyId=@ContractCompanyId,");
            strSql.Append("ContractCompany=@ContractCompany,");
            strSql.Append("ContractCode=@ContractCode,");
            strSql.Append("BillCode=@BillCode,");
            strSql.Append("InOrOut=@InOrOut,");
            strSql.Append("FundName=@FundName,");
            strSql.Append("CustomerName=@CustomerName,");
            strSql.Append("BillUnit=@BillUnit,");
            strSql.Append("BillMoney=@BillMoney,");
            strSql.Append("BillDate=@BillDate,");
            strSql.Append("Status=@Status,");
            strSql.Append("BillMemo=@BillMemo,");
            strSql.Append("UnusedReason=@UnusedReason,");
            strSql.Append("UnusedDate=@UnusedDate,");
            strSql.Append("UnusedPersonId=@UnusedPersonId,");
            strSql.Append("UnusedPerson=@UnusedPerson,");
            strSql.Append("BillDepartId=@BillDepartId,");
            strSql.Append("BillDepartName=@BillDepartName,");
            strSql.Append("BillCompanyId=@BillCompanyId,");
            strSql.Append("BillCompany=@BillCompany,");
            strSql.Append("BillCityId=@BillCityId,");
            strSql.Append("BillCity=@BillCity,");
            strSql.Append("InputTime=@InputTime,");
            strSql.Append("BillOprEmpId=@BillOprEmpId,");
            strSql.Append("BillOprEmpName=@BillOprEmpName,");
            strSql.Append("BillOprDepartId=@BillOprDepartId,");
            strSql.Append("BillOprDepartName=@BillOprDepartName,");
            strSql.Append("PayDate=@PayDate,");
            strSql.Append("PayMoney=@PayMoney,");
            strSql.Append("RemainMoney=@RemainMoney,");
            strSql.Append("PayOprEmpId=@PayOprEmpId,");
            strSql.Append("PayOprEmpName=@PayOprEmpName,");
            strSql.Append("PayOprEmpDepartId=@PayOprEmpDepartId,");
            strSql.Append("PayOprEmpDepart=@PayOprEmpDepart,");
            strSql.Append("InvoiceCode=@InvoiceCode,");
            strSql.Append("InvoiceDate=@InvoiceDate,");
            strSql.Append("InvoiceMemo=@InvoiceMemo,");
            strSql.Append("InvoiceOprEmpId=@InvoiceOprEmpId,");
            strSql.Append("InvoiceOprEmp=@InvoiceOprEmp,");
            strSql.Append("InvoiceOprDate=@InvoiceOprDate,");
            strSql.Append("IsFinish=@IsFinish,");
            strSql.Append("AchievementDate=@AchievementDate,");
            strSql.Append("LastCreateArchDate=@LastCreateArchDate,");
            strSql.Append("LastCreateArchEmp=@LastCreateArchEmp,");
            strSql.Append("AchievementYear=@AchievementYear,");
            strSql.Append("AchievementMonth=@AchievementMonth,");
            strSql.Append("FundManageType=@FundManageType,");
            strSql.Append("OrderPlaceId=@OrderPlaceId,");
            strSql.Append("OrderPlace=@OrderPlace,");
            strSql.Append("BillPlaceId=@BillPlaceId,");
            strSql.Append("BillPlace=@BillPlace,");
            strSql.Append("PayPlaceId=@PayPlaceId,");
            strSql.Append("PayPlace=@PayPlace,");
            strSql.Append("IsRecheck=@IsRecheck,");
            strSql.Append("PayBankType=@PayBankType,");
            strSql.Append("PayBankName=@PayBankName,");
            strSql.Append("PayBankNo=@PayBankNo");
            strSql.Append(" where BillId=@BillId ");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "BillId", DbType.Guid, model.BillId);
            db.AddInParameter(dbCommand, "ContractId", DbType.Guid, model.ContractId);
            db.AddInParameter(dbCommand, "ContractCityId", DbType.Guid, model.ContractCityId);
            db.AddInParameter(dbCommand, "ContractCity", DbType.AnsiString, model.ContractCity);
            db.AddInParameter(dbCommand, "ContractCompanyId", DbType.Guid, model.ContractCompanyId);
            db.AddInParameter(dbCommand, "ContractCompany", DbType.AnsiString, model.ContractCompany);
            db.AddInParameter(dbCommand, "ContractCode", DbType.AnsiString, model.ContractCode);
            db.AddInParameter(dbCommand, "BillCode", DbType.AnsiString, model.BillCode);
            db.AddInParameter(dbCommand, "InOrOut", DbType.Int32, model.InOrOut);
            db.AddInParameter(dbCommand, "FundName", DbType.AnsiString, model.FundName);
            db.AddInParameter(dbCommand, "CustomerName", DbType.AnsiString, model.CustomerName);
            db.AddInParameter(dbCommand, "BillUnit", DbType.AnsiString, model.BillUnit);
            db.AddInParameter(dbCommand, "BillMoney", DbType.Currency, model.BillMoney);
            db.AddInParameter(dbCommand, "BillDate", DbType.DateTime, model.BillDate);
            db.AddInParameter(dbCommand, "Status", DbType.Int32, model.Status);
            db.AddInParameter(dbCommand, "BillMemo", DbType.AnsiString, model.BillMemo);
            db.AddInParameter(dbCommand, "UnusedReason", DbType.AnsiString, model.UnusedReason);
            db.AddInParameter(dbCommand, "UnusedDate", DbType.DateTime, model.UnusedDate);
            db.AddInParameter(dbCommand, "UnusedPersonId", DbType.Guid, model.UnusedPersonId);
            db.AddInParameter(dbCommand, "UnusedPerson", DbType.AnsiString, model.UnusedPerson);
            db.AddInParameter(dbCommand, "BillDepartId", DbType.Guid, model.BillDepartId);
            db.AddInParameter(dbCommand, "BillDepartName", DbType.AnsiString, model.BillDepartName);
            db.AddInParameter(dbCommand, "BillCompanyId", DbType.Guid, model.BillCompanyId);
            db.AddInParameter(dbCommand, "BillCompany", DbType.AnsiString, model.BillCompany);
            db.AddInParameter(dbCommand, "BillCityId", DbType.Guid, model.BillCityId);
            db.AddInParameter(dbCommand, "BillCity", DbType.AnsiString, model.BillCity);
            db.AddInParameter(dbCommand, "InputTime", DbType.DateTime, model.InputTime);
            db.AddInParameter(dbCommand, "BillOprEmpId", DbType.Guid, model.BillOprEmpId);
            db.AddInParameter(dbCommand, "BillOprEmpName", DbType.AnsiString, model.BillOprEmpName);
            db.AddInParameter(dbCommand, "BillOprDepartId", DbType.Guid, model.BillOprDepartId);
            db.AddInParameter(dbCommand, "BillOprDepartName", DbType.AnsiString, model.BillOprDepartName);
            db.AddInParameter(dbCommand, "PayDate", DbType.DateTime, model.PayDate);
            db.AddInParameter(dbCommand, "PayMoney", DbType.Decimal, model.PayMoney);
            db.AddInParameter(dbCommand, "RemainMoney", DbType.Decimal, model.RemainMoney);
            db.AddInParameter(dbCommand, "PayOprEmpId", DbType.Guid, model.PayOprEmpId);
            db.AddInParameter(dbCommand, "PayOprEmpName", DbType.AnsiString, model.PayOprEmpName);
            db.AddInParameter(dbCommand, "PayOprEmpDepartId", DbType.Guid, model.PayOprEmpDepartId);
            db.AddInParameter(dbCommand, "PayOprEmpDepart", DbType.AnsiString, model.PayOprEmpDepart);
            db.AddInParameter(dbCommand, "InvoiceCode", DbType.AnsiString, model.InvoiceCode);
            db.AddInParameter(dbCommand, "InvoiceDate", DbType.DateTime, model.InvoiceDate);
            db.AddInParameter(dbCommand, "InvoiceMemo", DbType.AnsiString, model.InvoiceMemo);
            db.AddInParameter(dbCommand, "InvoiceOprEmpId", DbType.Guid, model.InvoiceOprEmpId);
            db.AddInParameter(dbCommand, "InvoiceOprEmp", DbType.AnsiString, model.InvoiceOprEmp);
            db.AddInParameter(dbCommand, "InvoiceOprDate", DbType.DateTime, model.InvoiceOprDate);
            db.AddInParameter(dbCommand, "IsFinish", DbType.Boolean, model.IsFinish);
            db.AddInParameter(dbCommand, "AchievementDate", DbType.AnsiString, model.AchievementDate);
            db.AddInParameter(dbCommand, "LastCreateArchDate", DbType.DateTime, model.LastCreateArchDate);
            db.AddInParameter(dbCommand, "LastCreateArchEmp", DbType.AnsiString, model.LastCreateArchEmp);
            db.AddInParameter(dbCommand, "AchievementYear", DbType.Int32, model.AchievementYear);
            db.AddInParameter(dbCommand, "AchievementMonth", DbType.Int32, model.AchievementMonth);
            db.AddInParameter(dbCommand, "FundManageType", DbType.AnsiString, model.FundManageType);
            db.AddInParameter(dbCommand, "OrderPlaceId", DbType.Guid, model.OrderPlaceId);
            db.AddInParameter(dbCommand, "OrderPlace", DbType.AnsiString, model.OrderPlace);
            db.AddInParameter(dbCommand, "BillPlaceId", DbType.Guid, model.BillPlaceId);
            db.AddInParameter(dbCommand, "BillPlace", DbType.AnsiString, model.BillPlace);
            db.AddInParameter(dbCommand, "PayPlaceId", DbType.Guid, model.PayPlaceId);
            db.AddInParameter(dbCommand, "PayPlace", DbType.AnsiString, model.PayPlace);
            db.AddInParameter(dbCommand, "IsRecheck", DbType.Boolean, model.IsRecheck);
            db.AddInParameter(dbCommand, "PayBankType", DbType.AnsiString, model.PayBankType);
            db.AddInParameter(dbCommand, "PayBankName", DbType.AnsiString, model.PayBankName);
            db.AddInParameter(dbCommand, "PayBankNo", DbType.AnsiString, model.PayBankNo);
            db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public static void Delete(Guid BillId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_Contract_Finance_BillInfo ");
            strSql.Append(" where BillId=@BillId ");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business_Search");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "BillId", DbType.Guid, BillId);
            db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static Chenxin.Lib.Business.Entity.ContractFinanceBillInfo GetModel(Guid BillId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select BillId,ContractId,ContractCityId,ContractCity,ContractCompanyId,ContractCompany,ContractCode,BillCode,InOrOut,FundName,CustomerName,BillUnit,BillMoney,BillDate,Status,BillMemo,UnusedReason,UnusedDate,UnusedPersonId,UnusedPerson,BillDepartId,BillDepartName,BillCompanyId,BillCompany,BillCityId,BillCity,InputTime,BillOprEmpId,BillOprEmpName,BillOprDepartId,BillOprDepartName,PayDate,PayMoney,RemainMoney,PayOprEmpId,PayOprEmpName,PayOprEmpDepartId,PayOprEmpDepart,InvoiceCode,InvoiceDate,InvoiceMemo,InvoiceOprEmpId,InvoiceOprEmp,InvoiceOprDate,IsFinish,AchievementDate,LastCreateArchDate,LastCreateArchEmp,AchievementYear,AchievementMonth,FundManageType,OrderPlaceId,OrderPlace,BillPlaceId,BillPlace,PayPlaceId,PayPlace,IsRecheck,PayBankType,PayBankName,PayBankNo from t_Contract_Finance_BillInfo ");
            strSql.Append(" where BillId=@BillId ");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business_Search");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "BillId", DbType.Guid, BillId);
            Chenxin.Lib.Business.Entity.ContractFinanceBillInfo model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = ReaderBind(dataReader);
                }
                dataReader.Close();
                dataReader.Dispose();
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select BillId,ContractId,ContractCityId,ContractCity,ContractCompanyId,ContractCompany,ContractCode,BillCode,InOrOut,FundName,CustomerName,BillUnit,BillMoney,BillDate,Status,BillMemo,UnusedReason,UnusedDate,UnusedPersonId,UnusedPerson,BillDepartId,BillDepartName,BillCompanyId,BillCompany,BillCityId,BillCity,InputTime,BillOprEmpId,BillOprEmpName,BillOprDepartId,BillOprDepartName,PayDate,PayMoney,RemainMoney,PayOprEmpId,PayOprEmpName,PayOprEmpDepartId,PayOprEmpDepart,InvoiceCode,InvoiceDate,InvoiceMemo,InvoiceOprEmpId,InvoiceOprEmp,InvoiceOprDate,IsFinish,AchievementDate,LastCreateArchDate,LastCreateArchEmp,AchievementYear,AchievementMonth,FundManageType,OrderPlaceId,OrderPlace,BillPlaceId,BillPlace,PayPlaceId,PayPlace,IsRecheck,PayBankType,PayBankName,PayBankNo ");
            strSql.Append(" FROM t_Contract_Finance_BillInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business_Search");
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public static DataSet GetBilledByContractId(string ContractId, string InOrOut, string FundName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select Sum(BillMoney) As BillMoney,Sum(PayMoney) As PayMoney");
            strSql.Append(" From t_Contract_Finance_BillInfo ");
            strSql.AppendFormat("Where ContractId = '{0}'", ContractId);
            strSql.AppendFormat(" And InOrOut = {0}", InOrOut);
            strSql.AppendFormat(" And FundName = '{0}'", FundName);
            strSql.AppendFormat(" And Status>{0}", -1);

            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business_Search");
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表（比DataSet效率高，推荐使用）
        /// </summary>
        public static List<Chenxin.Lib.Business.Entity.ContractFinanceBillInfo> GetListArray(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select BillId,ContractId,ContractCityId,ContractCity,ContractCompanyId,ContractCompany,ContractCode,BillCode,InOrOut,FundName,CustomerName,BillUnit,BillMoney,BillDate,Status,BillMemo,UnusedReason,UnusedDate,UnusedPersonId,UnusedPerson,BillDepartId,BillDepartName,BillCompanyId,BillCompany,BillCityId,BillCity,InputTime,BillOprEmpId,BillOprEmpName,BillOprDepartId,BillOprDepartName,PayDate,PayMoney,RemainMoney,PayOprEmpId,PayOprEmpName,PayOprEmpDepartId,PayOprEmpDepart,InvoiceCode,InvoiceDate,InvoiceMemo,InvoiceOprEmpId,InvoiceOprEmp,InvoiceOprDate,IsFinish,AchievementDate,LastCreateArchDate,LastCreateArchEmp,AchievementYear,AchievementMonth,FundManageType,OrderPlaceId,OrderPlace,BillPlaceId,BillPlace,PayPlaceId,PayPlace,IsRecheck,PayBankType,PayBankName,PayBankNo ");
            strSql.Append(" FROM t_Contract_Finance_BillInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            List<Chenxin.Lib.Business.Entity.ContractFinanceBillInfo> list = new List<Chenxin.Lib.Business.Entity.ContractFinanceBillInfo>();
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business_Search");
            using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBind(dataReader));
                }
                dataReader.Close();
                dataReader.Dispose();
            }
            return list;
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public static DataSet GetPartList(int PageSize, int PageIndex, string OrderField, string TableName, string strWhere, string FieldList, string TableFieldList, bool DoCount)
        {
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business_Search");
            DbCommand dbCommand = db.GetStoredProcCommand("PagerMultiTable");
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            db.AddInParameter(dbCommand, "OrderField", DbType.AnsiString, OrderField);
            db.AddInParameter(dbCommand, "TableName", DbType.AnsiString, TableName);
            db.AddInParameter(dbCommand, "StrWhere", DbType.AnsiString, strWhere);
            db.AddInParameter(dbCommand, "FieldList", DbType.AnsiString, FieldList);
            db.AddInParameter(dbCommand, "TableFieldList", DbType.AnsiString, TableFieldList);
            db.AddInParameter(dbCommand, "DoCount", DbType.Boolean, DoCount);
            return db.ExecuteDataSet(dbCommand);
        }

        /// <summary>
        /// 对象实体绑定数据
        /// </summary>
        public static Chenxin.Lib.Business.Entity.ContractFinanceBillInfo ReaderBind(IDataReader dataReader)
        {
            Chenxin.Lib.Business.Entity.ContractFinanceBillInfo model = new Chenxin.Lib.Business.Entity.ContractFinanceBillInfo();
            object ojb;
            ojb = dataReader["BillId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BillId = new Guid(ojb.ToString());
            }
            ojb = dataReader["ContractId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ContractId = new Guid(ojb.ToString());
            }
            ojb = dataReader["ContractCityId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ContractCityId = new Guid(ojb.ToString());
            }
            model.ContractCity = dataReader["ContractCity"].ToString();
            ojb = dataReader["ContractCompanyId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ContractCompanyId = new Guid(ojb.ToString());
            }
            model.ContractCompany = dataReader["ContractCompany"].ToString();
            model.ContractCode = dataReader["ContractCode"].ToString();
            model.BillCode = dataReader["BillCode"].ToString();
            ojb = dataReader["InOrOut"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.InOrOut = (int)ojb;
            }
            model.FundName = dataReader["FundName"].ToString();
            model.CustomerName = dataReader["CustomerName"].ToString();
            model.BillUnit = dataReader["BillUnit"].ToString();
            ojb = dataReader["BillMoney"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BillMoney = (decimal)ojb;
            }
            ojb = dataReader["BillDate"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BillDate = (DateTime)ojb;
            }
            ojb = dataReader["Status"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.Status = (int)ojb;
            }
            model.BillMemo = dataReader["BillMemo"].ToString();
            model.UnusedReason = dataReader["UnusedReason"].ToString();
            ojb = dataReader["UnusedDate"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.UnusedDate = (DateTime)ojb;
            }
            ojb = dataReader["UnusedPersonId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.UnusedPersonId = new Guid(ojb.ToString());
            }
            model.UnusedPerson = dataReader["UnusedPerson"].ToString();
            ojb = dataReader["BillDepartId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BillDepartId = new Guid(ojb.ToString());
            }
            model.BillDepartName = dataReader["BillDepartName"].ToString();
            ojb = dataReader["BillCompanyId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BillCompanyId = new Guid(ojb.ToString());
            }
            ojb = dataReader["OrderPlaceId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.OrderPlaceId = new Guid(ojb.ToString());
            }
            model.BillCompany = dataReader["BillCompany"].ToString();
            ojb = dataReader["BillCityId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BillCityId = new Guid(ojb.ToString());
            }
            model.BillCity = dataReader["BillCity"].ToString();
            ojb = dataReader["InputTime"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.InputTime = (DateTime)ojb;
            }
            ojb = dataReader["BillOprEmpId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BillOprEmpId = new Guid(ojb.ToString());
            }
            model.BillOprEmpName = dataReader["BillOprEmpName"].ToString();
            ojb = dataReader["BillOprDepartId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BillOprDepartId = new Guid(ojb.ToString());
            }
            model.BillOprDepartName = dataReader["BillOprDepartName"].ToString();
            ojb = dataReader["PayDate"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.PayDate = (DateTime)ojb;
            }
            ojb = dataReader["PayMoney"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.PayMoney = (decimal)ojb;
            }
            ojb = dataReader["RemainMoney"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.RemainMoney = (decimal)ojb;
            }
            ojb = dataReader["PayOprEmpId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.PayOprEmpId = new Guid(ojb.ToString());
            }
            model.PayOprEmpName = dataReader["PayOprEmpName"].ToString();
            ojb = dataReader["PayOprEmpDepartId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.PayOprEmpDepartId = new Guid(ojb.ToString());
            }
            model.PayOprEmpDepart = dataReader["PayOprEmpDepart"].ToString();
            model.InvoiceCode = dataReader["InvoiceCode"].ToString();
            ojb = dataReader["InvoiceDate"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.InvoiceDate = (DateTime)ojb;
            }
            model.InvoiceMemo = dataReader["InvoiceMemo"].ToString();
            ojb = dataReader["InvoiceOprEmpId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.InvoiceOprEmpId = new Guid(ojb.ToString());
            }
            model.InvoiceOprEmp = dataReader["InvoiceOprEmp"].ToString();
            ojb = dataReader["InvoiceOprDate"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.InvoiceOprDate = (DateTime)ojb;
            }
            ojb = dataReader["IsFinish"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.IsFinish = (bool)ojb;
            }
            model.AchievementDate = dataReader["AchievementDate"].ToString();
            ojb = dataReader["LastCreateArchDate"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.LastCreateArchDate = (DateTime)ojb;
            }
            model.LastCreateArchEmp = dataReader["LastCreateArchEmp"].ToString();
            ojb = dataReader["AchievementYear"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.AchievementYear = (int)ojb;
            }
            ojb = dataReader["AchievementMonth"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.AchievementMonth = (int)ojb;
            }
            model.FundManageType = dataReader["FundManageType"].ToString();
            model.OrderPlace = dataReader["OrderPlace"].ToString();
            ojb = dataReader["BillPlaceId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BillPlaceId = new Guid(ojb.ToString());
            }
            model.BillPlace = dataReader["BillPlace"].ToString();
            ojb = dataReader["PayPlaceId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.PayPlaceId = new Guid(ojb.ToString());
            }
            model.PayPlace = dataReader["PayPlace"].ToString();
            ojb = dataReader["IsRecheck"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.IsRecheck = (bool)ojb;
            }
            model.PayBankType = dataReader["PayBankType"].ToString();
            model.PayBankName = dataReader["PayBankName"].ToString();
            model.PayBankNo = dataReader["PayBankNo"].ToString();
            return model;
        }

        /// <summary>
        /// 获取付款金额
        /// </summary>
        /// <param name="ContractId">成交编号</param>
        /// <param name="FundName">开单款项名称</param>
        /// <returns>付款金额</returns>
        public static decimal GetPayedMoney(Guid ContractId, string FundName)
        {
            decimal dRet = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ISNULL(SUM(PayMoney),0) As PayMoney ");
            strSql.Append("FROM t_Contract_Finance_BillInfo ");
            strSql.Append("WHERE ContractId=@ContractId ");
            strSql.Append("And FundName=@FundName ");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Business_Search");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ContractId", DbType.Guid, ContractId);
            db.AddInParameter(dbCommand, "FundName", DbType.AnsiString, FundName);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    if (dataReader["PayMoney"] != null)
                    {
                        dRet = (decimal)dataReader["PayMoney"]; 
                    }
                }
                dataReader.Close();
                dataReader.Dispose();
            }
            return dRet;
        }
        #endregion  成员方法
    }
}
